import json

import binance.exceptions
import pymysql.err
from loguru import logger
import commons
from configs import Configs


class OrdersHelper:

    @staticmethod
    def get_orders_db():
        db = commons.get_db()
        cursor = db.cursor()
        cursor.execute(f"SELECT * FROM orders;")
        orders = {int(row[0]): row for row in cursor.fetchall()}  # 转换成dict方便查询
        db.close()
        return orders

    @staticmethod
    def get_order_by_id_cache(order_id):
        db_cache = commons.get_db_cache()
        cursor = db_cache.cursor()
        cursor.execute(f"SELECT * FROM orders WHERE id='{order_id}';")
        row = cursor.fetchone()
        detail = None
        try:
            detail = row[1]
        except TypeError as error:
            logger.debug(error)
        db_cache.close()
        return detail

    @staticmethod
    def get_orders_binance(limit=1000):
        configs = Configs()
        orders = commons.get_client().get_all_orders(symbol=configs.get_configs()["交易对"], limit=limit)
        orders = {order["orderId"]: order for order in orders}  # 转换成dict方便查询
        return orders

    def update_orders_db(self):
        orders_db = self.get_orders_db()
        orders_bi = self.get_orders_binance(limit=500)

        # 把从币安拉下来的交易记录刷新到本地数据库，状态不一致的才更新本地数据
        db_cache = commons.get_db_cache()
        cursor = db_cache.cursor()
        for order_id, order_bi in orders_bi.items():
            cursor.execute(f"SELECT * FROM orders WHERE id='{order_id}';")
            row = cursor.fetchone()
            if row:
                detail = json.loads(row[1])
                try:
                    if detail["status"] == order_bi['status']:
                        # 状态一致，不需要更新cache里订单的状态
                        continue
                except KeyError:
                    pass
            # 状态不一致，更新cache
            detail = json.dumps(order_bi)
            str_sql = f"INSERT INTO orders(id, detail) values('{order_id}', '{detail}') ON DUPLICATE KEY UPDATE detail='{detail}'"
            logger.debug(str_sql)
            cursor.execute(str_sql)
            db_cache.commit()
        db_cache.close()

        for order_id, order_db in orders_db.items():
            status_db = order_db[1]
            if status_db != "NEW":  # 如果不是限价单，则不用刷新状态
                continue
            logger.debug(f"刷新数据库中限价单状态：{order_id}")
            if order_id not in orders_bi:
                logger.error(f"数据库中有未完成的限价单，但币安历史记录中未找到此单：{order_id}")
                continue
            order_bi = orders_bi[order_id]
            status_bi = order_bi["status"]
            if status_bi.find("FILLED") != -1:
                status_bi = "FILLED"
            if status_db != status_bi:
                # 状态不一致，更新数据库、更新程序余额
                db = commons.get_db()
                cursor = db.cursor()
                sql = f'UPDATE orders SET status="{status_bi}" WHERE id="{order_id}";'
                logger.debug(sql)
                try:
                    cursor.execute(sql)
                except pymysql.err.DataError as err:
                    logger.error(err)
                db.commit()
                db.close()

                configs = Configs()
                balance = float(configs.get_configs()["程序余额"])
                if order_bi["side"] == 'BUY':
                    if status_bi == 'CANCELED':
                        configs.set_config_to_db("程序余额",
                                                 balance + float(order_bi["price"]) * float(order_bi["origQty"]))
                        configs.get_configs_from_db()
                        logger.debug(f"限价买单撤单，增加程序余额：{balance}->{configs.get_configs()['程序余额']}")
                else:  # side为SELL
                    if status_bi == 'FILLED':
                        configs.set_config_to_db("程序余额",
                                                 balance + float(order_bi["price"]) * float(
                                                     order_bi["cummulativeQuoteQty"]))
                        configs.get_configs_from_db()
                        logger.debug(f"限价卖单成交，增加程序余额：{balance}->{configs.get_configs()['程序余额']}")

    def cancel_order(self, client_order_id):
        ret = commons.get_client().cancel_order(symbol=Configs().get_configs()["交易对"],
                                                origClientOrderId=client_order_id)
        logger.debug(ret)
        self.update_orders_db()
        return ret

    @staticmethod
    def buy_limit(n, price, strategy_id):
        # 减少程序余额
        balance = float(Configs().get_configs()["程序余额"])

        if balance < n:
            logger.error(f"程序余额不足，取消挂限价买单: {balance, n}")
            return

        price = round(price, 2)
        quant = round(n / price, 4)

        try:
            order = commons.get_client().order_limit_buy(symbol=Configs().get_configs()["交易对"], quantity=quant,
                                                         price=price)
        except binance.exceptions.BinanceAPIException as err:
            logger.error(err)
            return

        if order['status'].find("FILLED") != -1:
            order['status'] = "FILLED"
        logger.debug(order)
        Configs().set_config_to_db("程序余额", round(balance - price * quant, 4))
        Configs().get_configs_from_db()
        logger.debug(f"限价买单挂单，减少程序余额：{balance}->{Configs().get_configs()['程序余额']}")

        db = commons.get_db()
        cursor = db.cursor()
        sql = f"INSERT INTO orders (id, status, relevant_order, strategy) " \
              f"VALUES('{order['orderId']}', '{order['status']}', '', {strategy_id});"
        logger.debug(sql)
        cursor.execute(sql)
        db.commit()
        db.close()
        return order

    @staticmethod
    def buy_market(n, price, strategy_id):
        """
        :param n:用多少个usd买入
        :param price:当前的btcusd价格，用于计算下单购买的btc数
        :param strategy_id:策略号
        :return:
        """
        logger.debug(f"(n, price, strategy_id)={(n, price, strategy_id)}")
        # 减少程序余额
        balance = float(Configs().get_configs()["程序余额"])

        if balance < n:
            logger.error(f"程序余额不足，取消挂市价买单: {balance, n}")
            return

        quant = round(n / price, 4)

        try:
            order = commons.get_client().order_market_buy(symbol=Configs().get_configs()["交易对"], quantity=quant)
        except binance.exceptions.BinanceAPIException as err:
            logger.error(err)
            return

        if order['status'].find("FILLED") != -1:
            order['status'] = "FILLED"
        logger.debug(order)
        Configs().set_config_to_db("程序余额", round(balance - price * quant, 4))
        Configs().get_configs_from_db()
        logger.debug(f"市价买单挂单，减少程序余额：{balance}->{Configs().get_configs()['程序余额']}")

        db = commons.get_db()
        cursor = db.cursor()
        sql = f"INSERT INTO orders (id, status, relevant_order, strategy) " \
              f"VALUES('{order['orderId']}', '{order['status']}', '', {strategy_id});"
        logger.debug(sql)
        cursor.execute(sql)
        db.commit()
        db.close()
        return order

    @staticmethod
    def sell_limit(quantity, price, strategy_id, relevant_order):
        price = round(price, 2)
        try:
            order = commons.get_client().order_limit_sell(symbol=Configs().get_configs()["交易对"], quantity=quantity,
                                                          price=price)
        except binance.exceptions.BinanceAPIException as err:
            logger.error(err)
            return

        logger.debug(order)

        db = commons.get_db()
        cursor = db.cursor()
        sql = f"INSERT INTO orders (id, status, relevant_order, strategy) " \
              f"VALUES('{order['orderId']}', '{order['status']}', '{relevant_order}', {strategy_id});"
        logger.debug(sql)
        cursor.execute(sql)
        db.commit()
        db.close()
        return order

    @staticmethod
    def sell_market(n, strategy_id, relevant_buy_order=None):
        balance = float(Configs().get_configs()["程序余额"])
        try:
            logger.debug(f"quantity={n}")
            order = commons.get_client().order_market_sell(symbol=Configs().get_configs()["交易对"], quantity=n)
        except binance.exceptions.BinanceAPIException as err:
            logger.error(err)
            return
        logger.debug(order)
        price = float(order["price"])
        quant = float(order["executedQty"])
        Configs().set_config_to_db("程序余额", round(balance - price * quant, 4))
        Configs().get_configs_from_db()
        logger.debug(f"市价卖单挂单，增加程序余额：{balance}->{Configs().get_configs()['程序余额']}")

        db = commons.get_db()
        cursor = db.cursor()
        sql = f"INSERT INTO orders (id, status, relevant_order, strategy) " \
              f"VALUES('{order['orderId']}', '{order['status']}', '{relevant_buy_order if relevant_buy_order is not None else ''}', {strategy_id});"
        logger.debug(sql)
        cursor.execute(sql)
        db.commit()
        db.close()

        return order

    @staticmethod
    def clear_db():
        db = commons.get_db()
        cursor = db.cursor()
        sql = "DELETE FROM orders WHERE status='FILLED';"
        logger.debug(sql)
        cursor.execute(sql)
        db.commit()
        db.close()


def test():
    order_helper = OrdersHelper()
    order_helper.get_orders_db()
    order_helper.get_orders_binance()
    order_helper.update_orders_db()
    logger.error(order_helper.get_orders_binance()[1330311981])


if __name__ == '__main__':
    test()
